﻿CREATE PROCEDURE [dbo].[ASMS_SP_QueryStockWarningPagedList]
	@businessId uniqueidentifier = null,
	@stationId uniqueidentifier= null,
	@warehouseId uniqueidentifier = null,
	@materialId uniqueidentifier = null,
	@start int,
	@limit int,
	@total int out
AS
select V.* from (
	select ROW_NUMBER() over (order by W.Name, M.Name) as _RowIndex,
		W.Id as WarehouseId,
		W.Name as WarehouseName,
		STV.[Value] as ThresholdValue,
		M.Name as MaterialName,
		M.Id as MaterialId,
		S.[Count] as [Count],
		MDI.Name as MaterialUnitName
	from ASMS_Warehouse as W 
		inner join ASMS_Stock as S on S.WarehouseId = W.Id
		left join ASMS_Material as M on M.Id = S.MaterialId
		left join ASMS_MasterDataItem as MDI on MDI.Code = M.Unit
		left join ASMS_StockThresholdValue as STV on STV.WarehouseId = W.Id and STV.MaterialId = S.MaterialId
	where (@businessId is null or (W.BusinessId = @businessId)) 
		and (@stationId is null or (W.StationId = @stationId))
		and (@warehouseId is null or (W.Id = @warehouseId))
		and (@materialId is null or (M.Id = @materialId))  and (S.[Count]<=0 or S.[Count] < STV.[Value])) as V
where V._RowIndex >@start and V._RowIndex <= @limit

set @total = (select Count(S.Id)
	from ASMS_Warehouse as W 
		inner join ASMS_Stock as S on S.WarehouseId = W.Id
		left join ASMS_Material as M on M.Id = S.MaterialId
		left join ASMS_StockThresholdValue as STV on STV.WarehouseId = W.Id and STV.MaterialId = S.MaterialId
	where (@businessId is null or (W.BusinessId = @businessId)) 
		and (@stationId is null or (W.StationId = @stationId))
		and (@warehouseId is null or (W.Id = @warehouseId))
		and (@materialId is null or (M.Id = @materialId))  and (S.[Count]<=0 or S.[Count] < STV.[Value]))
RETURN 0
